package com.dlut.preparedStatement.crud;

import com.dlut3.util.JDBCUtil;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertTest {
    /**
     * 批量插入
    addBatch(), executeBatch(), clearBatch()
     */
    @Test
    public void test2() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtil.getConnection();
            String sql = "insert into goods(name) values (?)";
            ps = conn.prepareStatement(sql);

            for (int i = 1; i <= 200000; i++) {
                ps.setObject(1, "name_" + i);

                //1. 攒 SQL
                ps.addBatch();
                if (i % 500 == 0){
                    //2. 执行Batch
                    ps.executeBatch();

                    //3. 清空Batch
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("系统执行的时间为" + (end - start));
            //2000个 -> 73307, 新方法2000个 -> 1478,  200000个 -> 16234
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(conn,ps);
        }
    }

    /**
     设置不允许自动提交
     */
    @Test
    public void test3() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtil.getConnection();
            String sql = "insert into goods(name) values (?)";
            ps = conn.prepareStatement(sql);

            conn.setAutoCommit(false);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, "name_" + i);
                ps.addBatch();
                if (i % 500 == 0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }

            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("系统执行的时间为" + (end - start));
            //2000个 -> 73307, 新方法2000个 -> 1478,  200000个 -> 16234
            //方法三   200000 -> 4733, 1,000,000 -> 13201
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(conn,ps);
        }
    }
}
